package com.twb.auth.tenant;

import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.Collection;
import java.util.List;
import java.util.Optional;
import java.util.Properties;
import java.util.stream.Stream;

import javax.servlet.http.HttpServletRequest;

import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlCommandType;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Plugin;
import org.apache.ibatis.plugin.Signature;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
import org.springframework.stereotype.Component;
import org.springframework.web.context.request.RequestContextHolder;

import com.twb.core.base.entity.BaseLoginUser;
import com.twb.core.constants.TwbConstants;
import com.twb.core.security.TwbSecurityUtil;

import net.sf.jsqlparser.expression.Alias;
import net.sf.jsqlparser.expression.BinaryExpression;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.Parenthesis;
import net.sf.jsqlparser.expression.StringValue;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.expression.operators.relational.EqualsTo;
import net.sf.jsqlparser.expression.operators.relational.ExpressionList;
import net.sf.jsqlparser.expression.operators.relational.MultiExpressionList;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.delete.Delete;
import net.sf.jsqlparser.statement.insert.Insert;
import net.sf.jsqlparser.statement.select.FromItem;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.select.SelectBody;
import net.sf.jsqlparser.statement.select.SelectExpressionItem;
import net.sf.jsqlparser.statement.select.SetOperationList;
import net.sf.jsqlparser.statement.select.SubSelect;
import net.sf.jsqlparser.statement.update.Update;
import net.sf.jsqlparser.util.TablesNamesFinder;

/**
 * @author DSY
 * @ClassName TenantInterceptor
 * @Description 租户拦截器
 * @date 2022年11月1日 下午5:24:41
 */
@Component
@ConditionalOnProperty(prefix = "tenant", name = "enabled", havingValue = "true")
@Intercepts({@Signature(type = StatementHandler.class, // 拦截构建sql语句的StatementHandler
    method = "prepare", // 里面的prepare方法
    args = {Connection.class, // 方法的参数
        Integer.class})})
public class TenantInterceptor implements Interceptor {

  private static final Logger log = LoggerFactory.getLogger(TenantInterceptor.class);

  /**
   * 跳过租户拦截器白名单
   */
  private static final List<String> whiteList = Arrays.asList("selectById");

  /**
   * 过滤的表信息
   */
  @Autowired
  private TenantConfigProperties tenantConfigProperties;

  @Autowired
  protected TwbSecurityUtil twbSecurityUtil;
  @Autowired
  HttpServletRequest request;

  /**
   * 根据表名判断是否进行过滤,默认全部不拦截
   *
   * @param tableName 表名
   * @return 是否进行过滤, false:表示忽略，true:需要解析多租户字段；优先判断需要拦截的表配置，再判断需要忽略的表配置
   */
  private boolean doTableFilter(String tableName) {
    // 需要拦截的表前缀
    List<String> tablePrefixs = tenantConfigProperties.getFilterTables();
    boolean doFilter = true;
    doFilter = Optional.ofNullable(tablePrefixs).map(Collection::stream).orElse(Stream.empty()).anyMatch(prefix -> wildcardMatch(prefix.toUpperCase(), tableName.toUpperCase()));
    if (!doFilter) {
      // 需要忽略的表名
      List<String> ignoreTables = tenantConfigProperties.getIgnoreTables();
      boolean tableNameCompare = Optional.ofNullable(ignoreTables).map(Collection::stream).orElse(Stream.empty()).anyMatch(name -> wildcardMatch(name.toUpperCase(), tableName.toUpperCase()));
      if (tableNameCompare) {
        return false;
      }
    }
    return doFilter;
  }

  /**
   * 通配符匹配
   *
   * @param pattern 通配符模式
   * @param str     待匹配的字符串
   * @return 匹配成功则返回true，否则返回false
   */
  private static boolean wildcardMatch(String pattern, String str) {
    int patternLength = pattern.length();
    int strLength = str.length();
    int strIndex = 0;
    char ch;
    for (int patternIndex = 0; patternIndex < patternLength; patternIndex++) {
      ch = pattern.charAt(patternIndex);
      if (ch == '*') {
        // 通配符星号*表示可以匹配任意多个字符
        while (strIndex < strLength) {
          if (wildcardMatch(pattern.substring(patternIndex + 1), str.substring(strIndex))) {
            return true;
          }
          strIndex++;
        }
      } else if (ch == '?') {
        // 通配符问号?表示匹配任意一个字符
        strIndex++;
        if (strIndex > strLength) {
          // 表示str中已经没有字符匹配?了。
          return false;
        }
      } else {
        if ((strIndex >= strLength) || (ch != str.charAt(strIndex))) {
          return false;
        }
        strIndex++;
      }
    }
    return (strIndex == strLength);
  }

  /**
   * intercept, 此处实现拦截逻辑
   */
  @Override
  public Object intercept(Invocation invocation) throws Throwable {
    long start = System.currentTimeMillis();
    log.debug("租户拦截器开始");
    // 拦截的StatementHandler, 所以获取的对象应该也是它
    StatementHandler handler = (StatementHandler) invocation.getTarget();

    // 取绑定的SQL脚本并打印
    BoundSql boundSql = handler.getBoundSql();
    String sql = boundSql.getSql();
    log.debug("Intercept SQL: {}", sql);

    String delegateSql = sql;
    MetaObject statementHandler = SystemMetaObject.forObject(handler);
    // 取Mapper文件定义
    MappedStatement mappedStatement = (MappedStatement) statementHandler.getValue("delegate.mappedStatement");

    //获取执行方法的位置
    String namespace = mappedStatement.getId();
    //获取mapper名称
    String className = namespace.substring(0, namespace.lastIndexOf("."));
    //获取方法名
    String methodName = namespace.substring(namespace.lastIndexOf(".") + 1, namespace.length());
    methodName = methodName.replace("_COUNT", "");

    if(whiteList.contains(methodName)){
      log.debug("租户拦截器未拦截 ==> 白名单");
      return invocation.proceed();
    }

    //获取当前mapper 的方法
    Method[] ms = Class.forName(className).getMethods();
    Boolean ignore = false;
    for (Method m : ms) {
      if (m.getName().equals(methodName)) {
        //获取注解  来判断是不是要拦截sql
        Tenant tenant = m.getAnnotation(Tenant.class);
        if (tenant != null) {
          ignore = tenant.ignore();
          break;
        }
        break;
      }
    }
    if (ignore) {
      log.debug("租户拦截器未拦截");
      return invocation.proceed();
    }

    // 如果不是http请求的查询则不进行拦截
    if (RequestContextHolder.getRequestAttributes() != null) {
      BaseLoginUser loginUser = this.twbSecurityUtil.getLoginUser(this.request);
      // 不是select方法、表名不符合规则不进行拦截
      if (loginUser == null || !(SqlCommandType.SELECT.equals(mappedStatement.getSqlCommandType()) || SqlCommandType.INSERT.equals(mappedStatement.getSqlCommandType())) || StringUtils.isBlank(delegateSql) || loginUser.isDeveloper()) {
        return invocation.proceed();
      }
      // 核心注入自定义脚本的方法, 具体往下看
      delegateSql = this.delegate(sql);
      statementHandler.setValue("delegate.boundSql.sql", delegateSql);
    }

    log.debug("Delegate SQL: {}", delegateSql);
    long end = System.currentTimeMillis();
    log.debug("租户拦截器截止，耗时：" + (end - start));
    return invocation.proceed();
  }

  private String delegate(String originSql) throws Exception {
    return this.rewrite(originSql);
  }

  /**
   * SQL重写路由
   *
   * @param originSql
   * @return
   * @throws Exception
   */
  private String rewrite(String originSql) throws Exception {
    Statement statement = CCJSqlParserUtil.parse(originSql);
    if (statement instanceof Insert) {
      return this.rewriteInsertSql(statement);
    } else if (statement instanceof Delete) {
      return this.rewriteDeleteSql(statement);
    } else if (statement instanceof Update) {
      return this.rewriteUpdateSql(statement);
    } else if (statement instanceof Select) {
      return this.rewriteSelectSql(statement);
    } else {
      // 自行实现异常
      throw new SQLException();
    }
  }

  private String rewriteInsertSql(Statement statement) {
    Insert insert = (Insert) statement;
    String tableName = insert.getTable().getName();
    BaseLoginUser loginUser = this.twbSecurityUtil.getLoginUser(this.request);
    if (loginUser != null && !loginUser.isDeveloper() && doTableFilter(tableName)) {
      String tenantId = loginUser.getTenantId();
      if (StringUtils.isNotBlank(tenantId)) {
        Boolean containsTenantId = false;
        List<Column> columns = insert.getColumns();
        for (Column column : columns) {
          String columnName = column.getColumnName();
          if (columnName.toUpperCase().equals(tenantConfigProperties.getColumnName().toUpperCase())) {
            containsTenantId = true;
            break;
          }
        }
        if (!containsTenantId) {
          insert.getColumns().add(new Column(tenantConfigProperties.getColumnName()));
          if (insert.getSelect() != null) {
            processPlainSelect((PlainSelect) insert.getSelect().getSelectBody(), true);
          } else {
            if (insert.getItemsList() instanceof MultiExpressionList) {
              for (ExpressionList expression : ((MultiExpressionList) insert.getItemsList()).getExpressionLists()) {
                expression.getExpressions().add(new StringValue(tenantId));
              }
            } else {
              ((ExpressionList) insert.getItemsList()).getExpressions().add(new StringValue(tenantId));
            }
          }
        }
      }
    }
    return insert.toString();
  }

  protected void processPlainSelect(PlainSelect plainSelect, boolean addColumn) {
    FromItem fromItem = plainSelect.getFromItem();
    if (fromItem instanceof Table) {
      if (addColumn) {
        plainSelect.getSelectItems().add(new SelectExpressionItem(new Column(tenantConfigProperties.getColumnName())));
      }
    }
  }

  private String rewriteDeleteSql(Statement statement) throws SQLException {
    Delete deleteStatement = (Delete) statement;
    Expression whereExpression = deleteStatement.getWhere();
    if (whereExpression == null) {
      throw new SQLException("Delete-Statement must be set conditions");
    }
    // 含左右表达式
    if (whereExpression instanceof BinaryExpression) {
      AndExpression andExpression = new AndExpression(this.newEqualTo(), new Parenthesis(whereExpression));

      deleteStatement.setWhere(andExpression);
    }
    return deleteStatement.toString();
  }

  private String rewriteUpdateSql(Statement statement) throws SQLException {
    Update updateStatement = (Update) statement;
    if (updateStatement.getWhere() == null) {
      throw new SQLException("Update-Statement must be set conditions");
    }

    TablesNamesFinder tableNameFinder = new TablesNamesFinder();
    List<String> tableNames = tableNameFinder.getTableList(statement);
    // select 1
    if (tableNames.size() == 0) {
      return updateStatement.toString();
    }

    for (String tableName : tableNames) {
      updateStatement.setWhere(this.newAndExpression(statement, tableName, updateStatement.getWhere()));
    }

    return updateStatement.toString();
  }

  private String rewriteSelectSql(Statement statement) {
    Select selectStatement = (Select) statement;

    TablesNamesFinder tablesNameFinder = new TablesNamesFinder();
    List<String> tableNames = tablesNameFinder.getTableList(selectStatement);
    // select 1 OR select now()
    if (tableNames.size() == 0) {
      return selectStatement.toString();
    }

    // 复杂查询, 譬如JOIN, 普通连表等
    PlainSelect plainSelect = (PlainSelect) selectStatement.getSelectBody();
    FromItem fromItem = plainSelect.getFromItem();
    if (fromItem instanceof SubSelect) {
      SubSelect subSelect = (SubSelect) fromItem;
      if (subSelect.getSelectBody() != null) {
        if (subSelect.getSelectBody() instanceof SetOperationList) {
          SetOperationList operationList = (SetOperationList) subSelect.getSelectBody();
          if (CollectionUtils.isNotEmpty(operationList.getSelects())) {
            List<SelectBody> plainSelects = operationList.getSelects();
            for (SelectBody selectBody : plainSelects) {
              PlainSelect select = (PlainSelect) selectBody;
              String mainTableName = dealPlainSelect(select);
              if (!doTableFilter(mainTableName)) {
                return selectStatement.toString();
              }
              if (select.getWhere() == null) {
                select.setWhere(this.newEqualTo(statement, mainTableName));
              } else {
                select.setWhere(this.newAndExpression(statement, mainTableName, select.getWhere()));
              }
            }
          }

        } else {
          PlainSelect select = (PlainSelect) subSelect.getSelectBody();
          String mainTableName = dealPlainSelect(select);
          if (!doTableFilter(mainTableName)) {
            return selectStatement.toString();
          }
          if (select.getWhere() == null) {
            select.setWhere(this.newEqualTo(statement, mainTableName));
          } else {
            select.setWhere(this.newAndExpression(statement, mainTableName, select.getWhere()));
          }
        }
      }
    } else if (fromItem instanceof Table) {
      String mainTableName = ((Table) plainSelect.getFromItem()).getName();
      if (!doTableFilter(mainTableName)) {
        return selectStatement.toString();
      }
      if (plainSelect.getWhere() == null) {
        plainSelect.setWhere(this.newEqualTo(statement, mainTableName));
      } else {
        plainSelect.setWhere(this.newAndExpression(statement, mainTableName, plainSelect.getWhere()));
      }
    }
    return selectStatement.toString();
  }

  /**
   * 多个子查询处理获取主表别名
   *
   * @param plainSelect
   * @return
   */
  private String dealPlainSelect(PlainSelect plainSelect) {
    String mainTableName = "";
    FromItem fromItem = plainSelect.getFromItem();
    if (fromItem instanceof Table) {
      mainTableName = ((Table) plainSelect.getFromItem()).getName();
      return mainTableName;
    } else {
      SubSelect subSelect = (SubSelect) fromItem;
      return dealPlainSelect((PlainSelect) subSelect.getSelectBody());
    }
  }

  private AndExpression newAndExpression(Statement statement, String tableName, Expression whereExpression) {
    EqualsTo equalsTo = this.newEqualTo(statement, tableName);
    // rewrite parent where expression
    return new AndExpression(equalsTo, new Parenthesis(whereExpression));
  }

  private EqualsTo newEqualTo() {
    EqualsTo equalsTo = new EqualsTo();
    equalsTo.setLeftExpression(new Column(tenantConfigProperties.getColumnName()));
    BaseLoginUser loginUser = this.twbSecurityUtil.getLoginUser(this.request);
    if (loginUser != null && !loginUser.isDeveloper()) {
      String tenantId = loginUser.getTenantId();
      if (StringUtils.isNotBlank(tenantId)) {
        equalsTo.setRightExpression(new StringValue(tenantId));
      } else {
        equalsTo.setRightExpression(new StringValue(TwbConstants.DEFAULT_TENANTID));
      }
    }
    return equalsTo;
  }

  private EqualsTo newEqualTo(Statement statement, String tableName) {
    EqualsTo equalsTo = new EqualsTo();
    String aliasName = this.getTableAlias(statement, tableName);
    equalsTo.setLeftExpression(new Column((aliasName == null ? "" : aliasName + '.') + tenantConfigProperties.getColumnName()));
    BaseLoginUser loginUser = this.twbSecurityUtil.getLoginUser(this.request);
    if (loginUser != null && !loginUser.isDeveloper()) {
      String tenantId = loginUser.getTenantId();
      if (StringUtils.isNotBlank(tenantId)) {
        equalsTo.setRightExpression(new StringValue(tenantId));
      } else {
        equalsTo.setRightExpression(new StringValue(TwbConstants.DEFAULT_TENANTID));
      }
    }
    return equalsTo;
  }

  private String getTableAlias(Statement stmt, String tableName) {
    String aliasName = null;
    if (stmt instanceof Insert) {
      return tableName;
    } else if (stmt instanceof Delete) {
      Delete deleteStatement = (Delete) stmt;
      if ((deleteStatement.getTable()).getName().equalsIgnoreCase(tableName)) {
        Alias alias = deleteStatement.getTable().getAlias();
        aliasName = alias != null ? alias.getName() : tableName;
      }
    } else if (stmt instanceof Update) {
      Update updateStatement = (Update) stmt;
      if (updateStatement.getTable().getName().equalsIgnoreCase(tableName)) {
        Alias alias = updateStatement.getTable().getAlias();
        aliasName = alias != null ? alias.getName() : tableName;
      }
    } else if (stmt instanceof Select) {
      Select select = (Select) stmt;
      PlainSelect plainSelect = (PlainSelect) select.getSelectBody();
      FromItem fromItem = plainSelect.getFromItem();
      if (fromItem instanceof SubSelect) {
        PlainSelect sel = (PlainSelect) ((SubSelect) fromItem).getSelectBody();
        String tabName = dealPlainSelect(sel);
        if ((tabName.equalsIgnoreCase(tableName))) {
          Alias alias = sel.getFromItem().getAlias();
          aliasName = alias != null ? alias.getName() : null;
        }
      } else if (fromItem instanceof Table) {
        if (((Table) fromItem).getName().equalsIgnoreCase(tableName)) {
          Alias alias = fromItem.getAlias();
          aliasName = alias != null ? alias.getName() : null;
        }
      }

    }
    return aliasName;
  }

  /**
   * plugin方法是拦截器用于封装目标对象的，通过该方法我们可以返回目标对象本身，也可以返回一个它的代理
   * <p>
   * 当返回的是代理的时候我们可以对其中的方法进行拦截来调用intercept方法 -- Plugin.wrap(target, this) 当返回的是当前对象的时候 就不会调用intercept方法，相当于当前拦截器无效
   */
  @Override
  public Object plugin(Object target) {
    return target instanceof StatementHandler ? Plugin.wrap(target, this) : target;
  }

  @Override
  public void setProperties(Properties properties) {
  }
}
